Re: Determining which index to create

--On jeudi 22 novembre 2001 01:04 +1100 Martijn van Oosterhout
<kleptog@svana.org> wrote:

> On Wed, Nov 21, 2001 at 12:53:09PM +0100, Eric Cholet wrote:
>> I should have mentionned I tried that, but it isn't being used:
>>
>> => \d dico_frs_motid_date
>>      Index "dico_frs_motid_date"
>>  Attribute |           Type
>> -----------+--------------------------
>>  motid     | integer
>>  date      | timestamp with time zone
>> btree
>>
>> => explain select * from dico_frs where motid=4742 order by date desc
>> limit  10;
>> NOTICE:  QUERY PLAN:
>>
>> Limit  (cost=0.00..17591.91 rows=10 width=16)
>>   ->  Index Scan Backward using dico_frs_date on dico_frs
>> (cost=0.00..20023641.63 rows=11382 width=16)
>
> Well, it is doing the scan backwards, which is good. But it's not using
> the index. If you drop dico_frs_date index, does it do it then?

=> explain select * from dico_frs where motid=4742 order by date desc limit
10;
NOTICE:  QUERY PLAN:

Limit  (cost=46172.25..46172.25 rows=10 width=16)
  ->  Sort  (cost=46172.25..46172.25 rows=11382 width=16)
        ->  Index Scan using dico_frs_motid_date on dico_frs
(cost=0.00..45405.39 rows=11382 width=16)


It's a bit better but still quite long, depending on how many rows for a
particular motid.
Dropping the "desc" in the "order by date" clause makes things much faster,
but I need the
results in reverse chronological order!

> Oh, and what version of postgres was this again?

=> select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.1.3 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3
(1 row)


Thanks for your help,
--
Eric Cholet